package com.samson.empapp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.samson.empapp.dbo.JobHistoryDbo;
import com.samson.empapp.util.DbUtil;


public class JobHistoryDao implements IJobHistoryDao {
	
	static Logger logger = Logger.getLogger(JobHistoryDao.class);
	@Override
	public JobHistoryDbo findById(int id) {
		JobHistoryDbo obj = null;
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		try {

			sb = new StringBuilder();
			sb.append("select EMPLOYEE_ID as employeeId, START_DATE as startDate, END_DATE as endDate, JOB_ID as jobId, DEPARTMENT_ID as departmentId "); 
			sb.append("from JOB_HISTORY "); 
			sb.append("where EMPLOYEE_ID='?' ");
			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				obj = new JobHistoryDbo();
				obj.setEmployeeId(rs.getInt("employeeId"));
				obj.setStartDate(rs.getDate("startDate"));
				obj.setJobId(rs.getString("jobId"));
				obj.setEndDate(rs.getDate("endDate"));
				obj.setDepartmentId(rs.getInt("departmentId"));
											
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("Exception" + e);
		} finally {
			DbUtil.closeDBConnection(pstmt, rs, con, sb);
		}
		

		return obj;
}

	

	@Override
	public List<JobHistoryDbo> findAll() {
		List<JobHistoryDbo> returnList = new ArrayList<JobHistoryDbo>();
		JobHistoryDbo obj = null;
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("SELECT EMPLOYEE_ID as employeeId, START_DATE as startDate, END_DATE as endDate, JOB_ID as jobId, DEPARTMENT_ID as departmentId "); 
			sb.append("from JOB_HISTORY ");
			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				obj = new JobHistoryDbo();
				obj.setEmployeeId(rs.getInt("employeeId"));
				obj.setStartDate(rs.getDate("startDate"));
				obj.setJobId(rs.getString("jobId"));
				obj.setEndDate(rs.getDate("endDate"));
				obj.setDepartmentId(rs.getInt("departmentId"));
				returnList.add(obj);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("Exception" + e);
		} finally {
			DbUtil.closeDBConnection(pstmt, rs, con, sb);
		}

		return returnList;

	}



	@Override
	public void insert(JobHistoryDbo dbo) throws Exception {
		JobHistoryDbo obj=null;
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("INSERT INTO \"HR\".\"JOB_HISTORY\" "); 
			sb.append("(EMPLOYEE_Id ,START_DATE ,END_DATE ,JOB_ID ,DEPARTMENT_ID) VALUES "); 
			sb.append("('"+obj.getEmployeeId()+"' /*not nullable*/,'"+obj.getStartDate()+"' /*not nullable*/,'"+obj.getEndDate()+"' /*not nullable*/,'"+obj.getJobId()+"','"+obj.getDepartmentId()+"') "); 
			
			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("Exception" + e);
			throw e;

		} finally {
			DbUtil.closeDBConnection(pstmt, con, sb);
	}

	}
	}
